# 连接数据库
# 建立数据库引擎

from sqlalchemy import create_engine
# 建立session会话
from sqlalchemy.orm import sessionmaker
# 引入逻辑操作符
from sqlalchemy import and_, or_
from models.user import User


class userDao:
    def __init__(self):
        host = "localhost"  # 主机名
        port = "3306"  # 端口号
        username = "root"  # 用户名
        password = "206369"  # 密码
        database = "go_db"  # 数据库名
        config = "?charset=utf8"
        db_connect_url = "mysql+pymysql://" \
                         + username + ":" + password + "@" + host + ":" + port + "/" + database + config
        engine = create_engine(db_connect_url)
        Session = sessionmaker(bind=engine)
        self.session = Session()

    # 增加用户
    def insertUser(self, users):
        # self.session.add(users)
        self.session.add_all(users)
        self.session.commit()  # 提交事务

    # 根据id删除用户
    def deleteUser(self, params):  # int[]， where user.id in (5,6,7)
        rowCount = self.session.query(User).filter(User.id.in_(params)).delete()  # 删除指定id的数据
        self.session.commit()
        return rowCount

    # 更新用户信息
    def updateUser(self, user):  # update user set user.address = %s where user.age > %s
        rowCount = self.session.query(User).filter(User.age > user.age).update({User.address: user.address})
        self.session.commit()
        return rowCount

    # 查询用户信息
    def queryUser(self, user):   # select * from user where user.age = %s and user.id = %s
        result = self.session.query(User).filter(and_(User.age == user.age, User.id == user.id)).all()  # List[User]
        self.session.commit()
        # 对结果进行序列化处理
        jsonResult = []
        for item in result:
            # item: User
            jsonResult.append(item.JsonMarshal())
        return jsonResult   # List[json格式的User对象]
